package cn.sylinx.hbatis.db.dialect.sql;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import cn.sylinx.hbatis.db.common.FS;
import cn.sylinx.hbatis.db.common.FluentSqlParams;
import cn.sylinx.hbatis.db.mapper.ModelBuilder;
import cn.sylinx.hbatis.exception.HbatisException;
import cn.sylinx.hbatis.ext.model.BaseModel;
import cn.sylinx.hbatis.ext.model.Model;
import cn.sylinx.hbatis.kit.Pair;
import cn.sylinx.hbatis.kit.StrKit;
import cn.sylinx.hbatis.kit.Tuple;
import cn.sylinx.hbatis.log.GLog;
import cn.sylinx.hbatis.plugin.model.ModelFabric;

public interface SqlBuilder {

	/**
	 * 获取分页sql
	 * 
	 * @param preSql
	 * @param pageNumber
	 * @param pageSize
	 * @return Tuple 0：获取总行数sql，1：查询数据sql，3：分页参数
	 */
	Tuple buildPaginatorSql(String preSql, int pageNumber, int pageSize);

	/**
	 * 构建删除sql
	 * 
	 * @param t
	 * @return
	 */
	public <T> Tuple buildDeleteSQL(T t);

	/**
	 * 构建插入sql
	 * 
	 * @param t
	 * @return
	 */
	public <T> Tuple buildInsertSQL(T t);

	/**
	 * 构建更新sql
	 * 
	 * @param t
	 * @return
	 */
	public <T> Tuple buildUpdateSQL(T t);

	/**
	 * 构建根据字段删除sql
	 * 
	 * @param kvList
	 * @param clz
	 * @return
	 */
	public Tuple buildDeleteByFieldSQL(List<Pair> kvList, Class<?> clz);

	/**
	 * 构建根据字段查询sql
	 * 
	 * @param kvList
	 * @param clz
	 * @return
	 */
	public Tuple buildQueryByFieldSQL(List<Pair> kvList, Class<?> clz);

	/**
	 * 构建根据字段查询sql
	 * 
	 * @param kvList
	 * @param clz
	 * @param fields
	 * @return
	 */
	public Tuple buildQueryByFieldSQL(List<Pair> kvList, Class<?> clz, String[] fields);

	/**
	 * 构建简单查询 select * from table
	 * 
	 * @param clz
	 * @return
	 */
	public String buildSimpleQuery(Class<?> clz);

	/**
	 * 构建简单查询 select field_name from table
	 * 
	 * @param clz
	 * @param fields
	 * @return
	 */
	public String buildSimpleQuery(Class<?> clz, String[] fields);

	/**
	 * 获取表对象
	 * 
	 * @param clz
	 * @return
	 */
	public String getTable(Class<?> clz);

	/**
	 * 获取表列名称
	 * 
	 * @param clz
	 * @param prop
	 * @return
	 */
	public String getTableColumn(Class<?> clz, String prop);

	/**
	 * 创建表ddl
	 * 
	 * @param clz
	 * @return
	 */
	public String[] buildCreateTableDDL(Class<?> clz);

	/**
	 * 创建修改表结构字段ddl
	 * 
	 * @param clz
	 * @param prop
	 * @return
	 */
	public String[] buildModifyTableColumnDDL(Class<?> clz, String prop);

	/**
	 * 创建添加表结构字段ddl
	 * 
	 * @param clz
	 * @param prop
	 * @return
	 */
	public String[] buildAddTableColumnDDL(Class<?> clz, String prop);

	/**
	 * 合法检测语句
	 * 
	 * @return
	 */
	public String buildValidateQuery();

	default Tuple buildSelectSQL(FS<?> fluentSql) {

		FluentSqlParams<?> sqlParams = fluentSql.build();
		StringBuilder sql = new StringBuilder();
		if (StrKit.isNotBlank(sqlParams.getHint())) {
			sql.append(sqlParams.getHint()).append(" ");
		}

		sql.append("SELECT");
		if (sqlParams.isDistinct()) {
			sql.append(" DISTINCT");
		}

		String singleField = null;
		Class<?> singleFieldClass = null;

		if (sqlParams.isSingleField()) {

			// 如果只取1列
			if (StrKit.isBlank(sqlParams.getSelectColumns())
					&& StrKit.isBlank(sqlParams.getSelectExpressionColumns())) {
				throw new HbatisException("need one column at least");
			}

			singleFieldClass = Object.class;

			if (StrKit.isNotBlank(sqlParams.getSelectColumns())) {
				// 首先使用selectColumns内容, 其次使用 selectExpressionColumns，native sql表达式
				singleField = sqlParams.getSelectColumns().split(",")[0].trim();

				String bingoAttr = null;
				Map<String, String> attrs = sqlParams.getAttrs();
				for (Entry<String, String> entry : attrs.entrySet()) {
					if (singleField.equals(entry.getKey()) || singleField.equals(entry.getValue())) {
						bingoAttr = entry.getKey();
					}
				}

				if (bingoAttr == null) {
					throw new HbatisException("no field specified");
				}

				Map<String, Field> fieldMap = ModelBuilder.getModelFabric(sqlParams.getModelClass()).getFieldMap();
				singleFieldClass = fieldMap.get(bingoAttr).getType();

			}

		}

		boolean hasSelected = false;
		if (StrKit.isNotBlank(sqlParams.getSelectColumns())) {
			sql.append(' ').append(sqlParams.getSelectColumns()).append(' ');
			hasSelected = true;
		} else if (sqlParams.getExcludedColumns() != null && !sqlParams.getExcludedColumns().isEmpty()) {
			sql.append(' ').append(
					ModelBuilder.buildColumnsByExcluded(sqlParams.getModelClass(), sqlParams.getExcludedColumns()))
					.append(' ');
			hasSelected = true;
		}

		if (StrKit.isNotBlank(sqlParams.getSelectExpressionColumns())) {
			// 如果表达式不为空
			sql.append(hasSelected ? "," : "").append(sqlParams.getSelectExpressionColumns());

		} else if (!hasSelected) {
			sql.append(" * ");
		}

		sql.append("FROM ").append(sqlParams.getTableName());
		if (sqlParams.getConditionSQL().length() > 0) {
			sql.append(" WHERE ").append(sqlParams.getConditionSQL().substring(FS.AND_STR.length()));
		}

		if (StrKit.isNotBlank(sqlParams.getGroupBy())) {
			sql.append(" GROUP BY").append(sqlParams.getGroupBy());
		}
		
		if (StrKit.isNotBlank(sqlParams.getOrderBy())) {
			sql.append(" ORDER BY").append(sqlParams.getOrderBy());
		}

		if (StrKit.isNotBlank(sqlParams.getLimitSQL())) {
			sql.append(sqlParams.getLimitSQL());
		}

		int len = sqlParams.getParamValues() == null || sqlParams.getParamValues().isEmpty() ? 0
				: sqlParams.getParamValues().size();
		Object[] params = null;

		if (len > 0) {
			params = new Object[len];
			sqlParams.getParamValues().toArray(params);
		}

		GLog.debug("sql:{}, params:{}", sql.toString(), params);

		return Tuple.apply(sql.toString(), params,
				(singleFieldClass == null ? sqlParams.getModelClass() : singleFieldClass));
	}

	default Pair buildUpdateSQL(FS<?> fluentSql) {

		FluentSqlParams<?> sqlParams = fluentSql.build();
		List<Object> paramsUpdate = new ArrayList<Object>();

		StringBuilder sql = new StringBuilder();
		if (StrKit.isNotBlank(sqlParams.getHint())) {
			sql.append(sqlParams.getHint()).append(" ");
		}

		sql.append("UPDATE ").append(sqlParams.getTableName()).append(" SET ");

		StringBuilder setSQL = new StringBuilder();

		boolean gmtUpdated = false;

		if (null != sqlParams.getUpdateColumnExpression() && !sqlParams.getUpdateColumnExpression().isEmpty()) {
			// native sql expression
			sqlParams.getUpdateColumnExpression()
					.forEach((key, value) -> setSQL.append(key).append(" = ").append(value).append(", "));

			if (!gmtUpdated && BaseModel.class.isAssignableFrom(sqlParams.getModelClass())) {
				// 设置更新时间
				String gmtModify = sqlParams.mapColumn("gmtModify");
				if (!sqlParams.getUpdateColumnExpression().containsKey(gmtModify)) {
					setSQL.append(gmtModify).append(" = ?, ");
					paramsUpdate.add(new Date());
					gmtUpdated = true;
				}
			}
		}

		if (null != sqlParams.getUpdateColumns() && !sqlParams.getUpdateColumns().isEmpty()) {
			
			sqlParams.getUpdateColumns().forEach((key, value) -> {
				if(value == null) {
					// 值为空情况
					setSQL.append(key).append(" = NULL, ");
				} else {
					setSQL.append(key).append(" = ?, ");
					paramsUpdate.add(value);
				}
			});

			if (!gmtUpdated && BaseModel.class.isAssignableFrom(sqlParams.getModelClass())) {
				// 设置更新时间
				String gmtModify = sqlParams.mapColumn("gmtModify");
				if (!sqlParams.getUpdateColumns().containsKey(gmtModify)) {
					setSQL.append(gmtModify).append(" = ?, ");
					paramsUpdate.add(new Date());
					gmtUpdated = true;
				}
			}

		} else {
			if (null != sqlParams.getModel()) {

				ModelFabric mf = ModelBuilder.getModelFabric(sqlParams.getModelClass());
				// 获取所有字段
				List<Field> fields = mf.getFields();
				Map<String, String> attrs = mf.getAttrMapping();

				// 获取可设置空字段值
				Set<String> nullableSets = new HashSet<String>();
				if (sqlParams.getModel() instanceof Model) {
					nullableSets = ((Model) sqlParams.getModel()).getNullableFields();
				}

				if (sqlParams.getModel() instanceof BaseModel) {
					((BaseModel) sqlParams.getModel()).setGmtModify(new Date());
				}

				try {
					for (Field field : fields) {

						field.setAccessible(true);
						String fieldName = field.getName();
						Object v = field.get(sqlParams.getModel());
						String f = attrs.get(fieldName);
						if (f != null) {// 找到字段
							boolean nullable = nullableSets.contains(fieldName);// 可为空
							if (v != null) {
								setSQL.append(f).append(" = ?, ");
								paramsUpdate.add(v);
							} else if (nullable) {
								// 可为空，则设置null值
								setSQL.append(f).append(" = NULL, ");
							}
						}
					}
				} catch (IllegalArgumentException | IllegalAccessException e) {
					throw new HbatisException("illegal argument or Access:", e);
				}
			}
		}

		sql.append(setSQL.substring(0, setSQL.length() - 2));
		if (sqlParams.getConditionSQL().length() > 0) {
			sql.append(" WHERE ").append(sqlParams.getConditionSQL().substring(FS.AND_STR.length()));
		}

		List<Object> paramsListFinal = new ArrayList<>();
		List<Object> paramsListPre = sqlParams.getParamValues();
		if (paramsListPre == null) {
			paramsListPre = new ArrayList<>();
		}
		paramsListFinal.addAll(paramsUpdate);
		paramsListFinal.addAll(paramsListPre);

		int len = paramsListFinal.size();

		Object[] params = null;

		if (len > 0) {
			params = new Object[len];
			paramsListFinal.toArray(params);
		}

		GLog.debug("sql:{}, params:{}", sql.toString(), params);

		return Pair.of(sql.toString(), params);
	}

	default Pair buildInsertSQL(FS<?> fluentSql) {

		FluentSqlParams<?> sqlParams = fluentSql.build();

		StringBuilder columnNames = new StringBuilder();
		StringBuilder placeholder = new StringBuilder();
		List<Object> insertParams = new ArrayList<Object>();

		StringBuilder sql = new StringBuilder();
		if (StrKit.isNotBlank(sqlParams.getHint())) {
			sql.append(sqlParams.getHint()).append(" ");
		}

		sql.append("INSERT INTO ").append(sqlParams.getTableName());
		boolean hasField = false;

		if (null != sqlParams.getUpdateColumns() && !sqlParams.getUpdateColumns().isEmpty()) {

			Map<String, Object> uc = sqlParams.getUpdateColumns();
			Set<Entry<String, Object>> entrySets = uc.entrySet();
			for (Entry<String, Object> entry : entrySets) {
				columnNames.append(",").append(entry.getKey());
				placeholder.append(",?");
				insertParams.add(entry.getValue());
				hasField = true;
			}

			if (BaseModel.class.isAssignableFrom(sqlParams.getModelClass())) {
				// 设置更新时间
				String gmtModify = sqlParams.mapColumn("gmtModify");
				if (!sqlParams.getUpdateColumns().containsKey(gmtModify)) {
					columnNames.append(",").append(gmtModify);
					placeholder.append(",?");
					insertParams.add(new Date());
				}
				// 设置插入时间
				String gmtCreate = sqlParams.mapColumn("gmtCreate");
				if (!sqlParams.getUpdateColumns().containsKey(gmtCreate)) {
					columnNames.append(",").append(gmtCreate);
					placeholder.append(",?");
					insertParams.add(new Date());
				}
			}

		} else {
			if (null != sqlParams.getModel()) {

				ModelFabric mf = ModelBuilder.getModelFabric(sqlParams.getModelClass());
				// 获取所有字段
				List<Field> fields = mf.getFields();
				Map<String, String> attrs = mf.getAttrMapping();
				if (BaseModel.class.isAssignableFrom(sqlParams.getModelClass())) {
					Date c = new Date();
					((BaseModel) sqlParams.getModel()).setGmtModify(c);
					((BaseModel) sqlParams.getModel()).setGmtCreate(c);
				}

				try {
					for (Field field : fields) {

						field.setAccessible(true);
						String fieldName = field.getName();
						Object v = field.get(sqlParams.getModel());
						String f = attrs.get(fieldName);
						if (f != null && v != null) {// 找到字段

							columnNames.append(",").append(f);
							placeholder.append(",?");
							insertParams.add(v);
							hasField = true;
						}
					}
				} catch (IllegalArgumentException | IllegalAccessException e) {
					throw new HbatisException("illegal argument or Access:", e);
				}
			}
		}

		if (!hasField) {
			throw new HbatisException("no insert field detected");
		}

		sql.append("(").append(columnNames.substring(1)).append(")").append(" VALUES (")
				.append(placeholder.substring(1)).append(")");

		List<Object> paramsListFinal = new ArrayList<>();
		paramsListFinal.addAll(insertParams);
		int len = paramsListFinal.size();
		Object[] params = null;

		if (len > 0) {
			params = new Object[len];
			paramsListFinal.toArray(params);
		}

		GLog.debug("sql:{}, params:{}", sql.toString(), params);
		return Pair.of(sql.toString(), params);
	}

	default Pair buildDeleteSQL(FS<?> fluentSql) {

		FluentSqlParams<?> sqlParams = fluentSql.build();
		List<Object> paramsDelete = new ArrayList<Object>();

		StringBuilder sql = new StringBuilder();
		if (StrKit.isNotBlank(sqlParams.getHint())) {
			sql.append(sqlParams.getHint()).append(" ");
		}

		sql.append("DELETE FROM ").append(sqlParams.getTableName());

		if (sqlParams.getConditionSQL().length() > 0) {
			sql.append(" WHERE ").append(sqlParams.getConditionSQL().substring(FS.AND_STR.length()));
			List<Object> paramsListPre = sqlParams.getParamValues();
			if (paramsListPre == null) {
				paramsListPre = new ArrayList<>();
			}
			paramsDelete.addAll(paramsListPre);
		} else {
			if (null != sqlParams.getModel()) {

				StringBuilder columnNames = new StringBuilder();
				ModelFabric mf = ModelBuilder.getModelFabric(sqlParams.getModelClass());
				// 获取所有字段
				List<Field> fields = mf.getFields();
				Map<String, String> attrs = mf.getAttrMapping();

				try {
					for (Field field : fields) {

						field.setAccessible(true);
						String fieldName = field.getName();
						Object v = field.get(sqlParams.getModel());
						String f = attrs.get(fieldName);
						if (f != null && v != null) {
							columnNames.append(f).append(" = ? and ");
							paramsDelete.add(v);
						}
					}
				} catch (IllegalArgumentException | IllegalAccessException e) {
					throw new HbatisException("illegal argument or Access:", e);
				}

				if (columnNames.length() > 0) {
					sql.append(" WHERE ").append(columnNames.substring(0, columnNames.length() - 5));
				}
			}
		}

		List<Object> paramsListFinal = new ArrayList<>();
		paramsListFinal.addAll(paramsDelete);
		int len = paramsListFinal.size();

		Object[] params = null;

		if (len > 0) {
			params = new Object[len];
			paramsListFinal.toArray(params);
		}

		GLog.debug("sql:{}, params:{}", sql.toString(), params);

		return Pair.of(sql.toString(), params);
	}

	default Pair buildCountSQL(FS<?> fluentSql) {

		FluentSqlParams<?> sqlParams = fluentSql.build();

		if (!sqlParams.isCount()) {
			throw new HbatisException("not count sql");
		}

		StringBuilder sql = new StringBuilder();
		if (StrKit.isNotBlank(sqlParams.getHint())) {
			sql.append(sqlParams.getHint()).append(" ");
		}

		sql.append("SELECT  COUNT(*) ");

		sql.append("FROM ").append(sqlParams.getTableName());
		if (sqlParams.getConditionSQL().length() > 0) {
			sql.append(" WHERE ").append(sqlParams.getConditionSQL().substring(FS.AND_STR.length()));
		}

		int len = sqlParams.getParamValues() == null || sqlParams.getParamValues().isEmpty() ? 0
				: sqlParams.getParamValues().size();
		Object[] params = null;

		if (len > 0) {
			params = new Object[len];
			sqlParams.getParamValues().toArray(params);
		}

		GLog.debug("sql:{}, params:{}", sql.toString(), params);

		return Pair.apply(sql.toString(), params);
	}
}
